const Router = require('koa-router');
const router = new Router();
const tool = require('../../tools/tool')
const {db, QueryTypes} = require("../../models/db");


// 经营历程
router.post('/report/jylc', async  ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let tenantId = user.tenantId;
    let excel = body.excel ? 1 : 0;
    let search = body.formData;
    let beginDate = search.beginDate ? search.beginDate : '';
    let endDate = search.endDate ? search.endDate : '';
    if(!beginDate) return ctx.err('请选择开始日期')
    if(!endDate) return ctx.err('请选择结束日期')
    let searchTxt = search.searchTxt ? search.searchTxt : '';
    let buId = search.buId ? search.buId : 0;
    let invId = search.invId ? search.invId : 0;
    let accountedBy = search.accountedBy ? search.accountedBy : 0;
    let doBy = search.doBy ? search.doBy : 0;
    let transType = search.transType ? search.transType : 0;

    let pageSize = body.pageSize ?body.pageSize:200;
    let currentPage = body.currentPage ?body.currentPage:1;
    let skip = (currentPage - 1) *pageSize;

    let sqls =  `select * from v_invoice where tenantId=${tenantId} and billState='accounted' and billDate between '${beginDate}' and '${endDate}' `;
    if (searchTxt) {
        sqls += ` and searchTxt like '%${searchTxt}%' `;
    }
    if (transType) {
        sqls += ` and transType=${transType} `;
    }
    if (buId) {
        sqls += ` and buId=${buId} `;
    }
    if (doBy) {
        sqls += ` and doBy=${doBy} `;
    }
    if (accountedBy) {
        sqls += ` and accountedBy=${accountedBy} `;
    }

    if (excel) {
        sqls += " order by billDate asc,transType asc, id asc";
        let list = await db.query(sqls, {type: QueryTypes.SELECT})
        return tool.doExcelOut(ctx, list)
    } else {
        let total = await db.query(`select count(*) total from (${sqls}) a `, {type: QueryTypes.SELECT, plain: true})
        sqls += " order by billDate asc,transType asc, id asc";
        sqls += ` offset ${skip} rows fetch next ${pageSize} rows only; `
        let list = await db.query(sqls, {type: QueryTypes.SELECT})
        return ctx.suc('', {
            total: total ? total.total: 0,
            list
        });
    }
})


module.exports = router;
